﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using CNCSoft.EData;
using System.Data.SqlClient;

using Common;

namespace CNCSoft.ErpApps.TMS.Academic
{
    public partial class frmThongTinKTX : Form
    {
        
        DataTable dt;

        public frmThongTinKTX()
        {
            InitializeComponent();
        }

        private void txtMaNienHoc_TextChanged(object sender, EventArgs e)
        {
            //Load DM_LOP
            try
            {
                if (txtMaNienHoc.Text == "") return;
                DataTable dt = new DataTable();
                SQLHelper.Fill(dt, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_LOP WHERE MaNienHoc = '" + txtMaNienHoc.Text + "' Order by MaKhoi, STT");

                cboLop.DataSource = dt;
                cboLop.DisplayMember = "TenLop";
                cboLop.ValueMember = "MaLop";
            }
            catch (Exception ex)
                { MessageBox.Show(ex.Message); }

            //Load DM_SOPHONG_NOITRU
            try
            {
                if (txtMaNienHoc.Text == "") return;
                DataTable dt = new DataTable();
                SQLHelper.Fill(dt, GlobalVariable.WSInfo.ConnectionString, "select * from DM_PHONGNOITRU Order by MaPhongNoiTru");

                cboSoPhong.DataSource = dt;
                cboSoPhong.DisplayMember = "TenPhongNoiTru";
                cboSoPhong.ValueMember = "MaPhongNoiTru";
            }
            catch (Exception ex)
                { MessageBox.Show(ex.Message); }
        }

        private void cboLop_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                GetData(cboLop.SelectedValue.ToString());
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        SqlDataAdapter dataAdapter = new SqlDataAdapter();
        DataSet ds = new DataSet();

        private void GetData(string strMaLop)
        {
            if (strMaLop == "" ||  strMaLop.IndexOf("System")>=0 ) return;
            try
            {
                if (ds.Tables["HOCSINH_NOITRU"] != null)
                    ds.Tables["HOCSINH_NOITRU"].Clear();
                if (ds.Tables["HOCSINH_DUARUOC"] != null)
                    ds.Tables["HOCSINH_DUARUOC"].Clear();
                if (ds.Tables["HOCSINH"] != null)
                    ds.Tables["HOCSINH"].Clear();

                //Thong tin HS
                //Kiem tra: Neu TIEUHOC thi Lay tat ca hoc sinh, neu khong thi lay hoc sinh noi tru (The dua ruoc ap dung cho 2 TH: Hoc sinh Tieu hoc va HS noi tru)
                string strHOCSINH = "";
                int intCap =  (int)SQLHelper.ExecuteScalar(GlobalVariable.WSInfo.ConnectionString, "SELECT MaCap FROM DM_LOP WHERE MaLop = '" + strMaLop + "'");
                if (intCap == 1)  //Hoc sinh TIEU HOC
                {
                    strHOCSINH = "select HOCSINH_LOP.MaHocTap, HOCSINH_LOP.MaHocSinh, MaLop,  " +
                                                "HoLot, Ten, case when GioiTinh = 1 then N'Nam' else N'Nữ' end GioiTinh " +
                                        "from HOCSINH_LOP " +
                                        "join HOCSINH on HOCSINH_LOP.MaHocSinh = HOCSINH.MaHocSinh " +
                                        "where MaLop = '" + strMaLop + "' and MaTrangThai = 'SS' ";
                }
                else //Hoc sinh  NOI TRU
                {
                    strHOCSINH = "select HOCSINH_LOP.MaHocTap, HOCSINH_LOP.MaHocSinh, MaLop,  " +
                                                "HoLot, Ten, case when GioiTinh = 1 then N'Nam' else N'Nữ' end GioiTinh " +
                                        "from HOCSINH_LOP " +
                                        "join HOCSINH on HOCSINH_LOP.MaHocSinh = HOCSINH.MaHocSinh " +
                                        "where MaLop = '" + strMaLop + "' and MaCheDo = 'NO' and MaTrangThai = 'SS' ";
                }

                string strHOCSINH_NOITRU = "select MaHocTap, " +
                                            "MaNoiTru, SoPhong, NgayVaoNoiTru, PhongDoBai " +
                                            "from HOCSINH_LOP_NOITRU " + 
                                            "where MaHocTap in (select MaHocTap from ("  + strHOCSINH + ") HS ) "
                                            ;
                //Thong tin dua ruoc
                string strHOCSINH_DUARUOC = "select SoTheDuaRuoc, MaHocTap, HoTenNguoiDuaRuoc, MaQuanHe, SoCMND, SoDienThoai " +
                                            "from HOCSINH_LOP_NOITRU_DUARUOC " +
                                            "where MaHocTap in (select MaHocTap from (" + strHOCSINH + ") HS ) "  ;

        
                dataAdapter = new SqlDataAdapter(strHOCSINH, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH");

                dataAdapter = new SqlDataAdapter(strHOCSINH_NOITRU, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH_NOITRU");

                dataAdapter = new SqlDataAdapter(strHOCSINH_DUARUOC, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH_DUARUOC");

                if (ds.Relations.Count == 0) 
                {
                    ds.Tables["HOCSINH"].PrimaryKey = new DataColumn[] { ds.Tables["HOCSINH"].Columns["MaHocTap"] };
                    ds.Tables["HOCSINH_NOITRU"].PrimaryKey = new DataColumn[] { ds.Tables["HOCSINH"].Columns["MaNoiTru"] };
                    ds.Tables["HOCSINH_DUARUOC"].PrimaryKey = new DataColumn[] { ds.Tables["HOCSINH_DUARUOC"].Columns["SoTheDuaRuoc"] };

                    // Set relations
                    ds.Relations.Add("HS_NT", ds.Tables["HOCSINH"].Columns["MaHocTap"],
                        ds.Tables["HOCSINH_NOITRU"].Columns["MaHocTap"]);
                    ds.Relations.Add("HS_DR", ds.Tables["HOCSINH"].Columns["MaHocTap"],
                        ds.Tables["HOCSINH_DUARUOC"].Columns["MaHocTap"]);
                }

                grid_DSHSNoiTru.DataSource = ds;
                grid_DSHSNoiTru.DataMember = "HOCSINH";

                grid_HocSinh.DataSource = ds;
                grid_HocSinh.DataMember = "HOCSINH.HS_NT";

                grid_NguoiDuaRuoc.DataSource = ds;
                grid_NguoiDuaRuoc.DataMember = "HOCSINH.HS_DR";


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void GetDataTheoSoPhongNoiTru(string strSoPhong)
        {
            if (strSoPhong == "" || strSoPhong.IndexOf("System") >= 0) return;
            try
            {
                if (ds.Tables["HOCSINH_NOITRU"] != null)
                    ds.Tables["HOCSINH_NOITRU"].Clear();
                if (ds.Tables["HOCSINH_DUARUOC"] != null)
                    ds.Tables["HOCSINH_DUARUOC"].Clear();
                if (ds.Tables["HOCSINH"] != null)
                    ds.Tables["HOCSINH"].Clear();

                //Thong tin HS
                //Kiem tra: Neu TIEUHOC thi Lay tat ca hoc sinh, neu khong thi lay hoc sinh noi tru (The dua ruoc ap dung cho 2 TH: Hoc sinh Tieu hoc va HS noi tru)
                string strHOCSINH = "";
                strHOCSINH = "select	HOCSINH_LOP_NOITRU.MaHocTap, HOCSINH_LOP.MaHocSinh, MaLop, HoLot, Ten,   " +
                                            "case when GioiTinh = 1 then N'Nam' else N'Nữ' end GioiTinh " +
                                "from HOCSINH_LOP_NOITRU  " +
                                "join HOCSINH_LOP on HOCSINH_LOP_NOITRU.MaHocTap = HOCSINH_LOP.MaHocTap " +
                                "join HOCSINH on HOCSINH_LOP.MaHocSinh = HOCSINH.MaHocSinh " +
                                "where SoPhong = '" + strSoPhong + "'";

                string strHOCSINH_NOITRU = "select MaHocTap, " +
                                            "MaNoiTru, SoPhong, NgayVaoNoiTru, PhongDoBai " +
                                            "from HOCSINH_LOP_NOITRU " +
                                            "where MaHocTap in (select MaHocTap from (" + strHOCSINH + ") HS ) "
                                            ;
                //Thong tin dua ruoc
                string strHOCSINH_DUARUOC = "select SoTheDuaRuoc, MaHocTap, HoTenNguoiDuaRuoc, MaQuanHe, SoCMND, SoDienThoai " +
                                            "from HOCSINH_LOP_NOITRU_DUARUOC " +
                                            "where MaHocTap in (select MaHocTap from (" + strHOCSINH + ") HS ) ";


                dataAdapter = new SqlDataAdapter(strHOCSINH, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH");

                dataAdapter = new SqlDataAdapter(strHOCSINH_NOITRU, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH_NOITRU");

                dataAdapter = new SqlDataAdapter(strHOCSINH_DUARUOC, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH_DUARUOC");

                if (ds.Relations.Count == 0)
                {
                    ds.Tables["HOCSINH"].PrimaryKey = new DataColumn[] { ds.Tables["HOCSINH"].Columns["MaHocTap"] };
                    ds.Tables["HOCSINH_NOITRU"].PrimaryKey = new DataColumn[] { ds.Tables["HOCSINH"].Columns["MaNoiTru"] };
                    ds.Tables["HOCSINH_DUARUOC"].PrimaryKey = new DataColumn[] { ds.Tables["HOCSINH_DUARUOC"].Columns["SoTheDuaRuoc"] };

                    // Set relations
                    ds.Relations.Add("HS_NT", ds.Tables["HOCSINH"].Columns["MaHocTap"],
                        ds.Tables["HOCSINH_NOITRU"].Columns["MaHocTap"]);
                    ds.Relations.Add("HS_DR", ds.Tables["HOCSINH"].Columns["MaHocTap"],
                        ds.Tables["HOCSINH_DUARUOC"].Columns["MaHocTap"]);
                }

                grid_DSHSNoiTru.DataSource = ds;
                grid_DSHSNoiTru.DataMember = "HOCSINH";

                grid_HocSinh.DataSource = ds;
                grid_HocSinh.DataMember = "HOCSINH.HS_NT";

                grid_NguoiDuaRuoc.DataSource = ds;
                grid_NguoiDuaRuoc.DataMember = "HOCSINH.HS_DR";


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
            

        private void frmThongTinKTX_Load(object sender, EventArgs e)
        {
            txtMaNienHoc.Text = GlobalVariable.WSInfo.getAppProperty("MaNienHocDefault", "").ToString();

            //DM_PHONGNOITRU
            DataTable dt_PNT = new DataTable();
            SQLHelper.Fill(dt_PNT, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_PHONGNOITRU");
            repositoryItemLookUpEdit_PhongNoiTru.DataSource = dt_PNT;

            //DM_PHONG (Do bai)
            DataTable dt_PDB = new DataTable();
            SQLHelper.Fill(dt_PDB, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_PHONG");
            repositoryItemLookUpEdit_PhongDoBai.DataSource = dt_PDB;

            //DM_QUANHE
            DataTable dt_QH = new DataTable();
            SQLHelper.Fill(dt_QH, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_QUANHE");
            repositoryItemLookUpEdit_QuanHe.DataSource = dt_QH;
        }


        private void DuyetRowState()
        {
            if (dt == null || dt.Rows.Count == 0)
                return;
            try
            {
                //dataTable.AcceptChanges();

                foreach (DataRow row in dt.Rows)
                    if (row["ID"] == DBNull.Value)
                    {
                        row.AcceptChanges();
                        row.SetAdded();
                    }
            }
            catch { }
        }


        private void cmdCapNhat_Click(object sender, EventArgs e)
        {
            try
            {
                //DuyetRowState();

                
                SqlConnection cnn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
                
                //Update HOCSINH_LOP_NOITRU
                SqlDataAdapter sqldaHS = new SqlDataAdapter("SELECT * FROM HOCSINH_LOP_NOITRU", cnn);
                DataHelper.buildDataCommands(ref sqldaHS);
                sqldaHS.Update(ds, "HOCSINH_NOITRU");

                //Update HOCSINH_LOP_NOITRU_DUARUOC
                SqlDataAdapter sqldaDR = new SqlDataAdapter("SELECT * FROM HOCSINH_LOP_NOITRU_DUARUOC", cnn);
                DataHelper.buildDataCommands(ref sqldaDR);
                sqldaDR.Update(ds, "HOCSINH_DUARUOC");
                
                //Load lai           
                if (optLop.Checked)
                    GetData(cboLop.SelectedValue.ToString());
                else
                    GetDataTheoSoPhongNoiTru(cboSoPhong.SelectedValue.ToString());
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void cmdBoQua_Click(object sender, EventArgs e)
        {
            //Load lai           
            if (optLop.Checked)
                GetData(cboLop.SelectedValue.ToString());
            else
                GetDataTheoSoPhongNoiTru(cboSoPhong.SelectedValue.ToString());
        }

        private void cboSoPhong_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                GetDataTheoSoPhongNoiTru(cboSoPhong.SelectedValue.ToString());
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void optLop_CheckedChanged(object sender, EventArgs e)
        {
            cboLop.Enabled = optLop.Checked;
        }

        private void optPhong_CheckedChanged(object sender, EventArgs e)
        {
            cboSoPhong.Enabled = optPhong.Checked;
        }



    }
}
